﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class HoaDonDAO
    {
        public List<DTO.HoaDonDTO> SelectAll_returnList()
        {
          
            List<DTO.HoaDonDTO> lst = new List<HoaDonDTO>();

                OleDbConnection conn = DataProvider.ConnectDB();
                conn.Open();
                OleDbCommand cmd = new OleDbCommand("select * from HoaDon", conn);
                OleDbDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    DTO.HoaDonDTO hdDTO = new HoaDonDTO();
                    hdDTO.MaHD = rd.GetInt32(0);
                    hdDTO.NgayHD = rd.GetDateTime(1);
                    hdDTO.SoHD = rd.GetString(2);
                    hdDTO.MaKH = rd.GetInt32(3);
                    hdDTO.TenKH = rd.GetString(4);
                    hdDTO.ThanhTien = rd.GetDouble(5);
                    hdDTO.MaNV = rd.GetInt32(6);
                    hdDTO.LoaiKH = rd.GetBoolean(7);
                    hdDTO.TenNV = rd.GetString(8);
                    lst.Add(hdDTO);
                }
                conn.Close();

            return lst;
        }
        public System.Data.DataSet SelectAll_returnDataSet()
        {
            System.Data.DataSet ds=new System.Data.DataSet();
            OleDbConnection conn = DataProvider.ConnectDB();
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from HoaDon", conn);
            da.Fill(ds, "HoaDon");
            da.Dispose();
            conn.Close();
            return ds;
        }
        public List<DTO.HoaDonDTO> LayHoaDonTheoNgay(DateTime batdau,DateTime ketthuc)
        {

            List<DTO.HoaDonDTO> lst = new List<HoaDonDTO>();

            OleDbConnection conn = DataProvider.ConnectDB();
            conn.Open();
            string sql = "select * from HoaDon where NgayHD between " +"#"+batdau.ToShortDateString() +"#"+ " and " +"#"+ketthuc.ToShortDateString()+"# ";
           // string sql = "select * from HoaDon where NgayHD between #5/11/2013# and #5/11/2013#";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                DTO.HoaDonDTO hdDTO = new HoaDonDTO();
                hdDTO.MaHD = rd.GetInt32(0);
                hdDTO.NgayHD = rd.GetDateTime(1);
                hdDTO.SoHD = rd.GetString(2);
                hdDTO.MaKH = rd.GetInt32(3);
                hdDTO.TenKH = rd.GetString(4);
                hdDTO.ThanhTien = rd.GetDouble(5);
                hdDTO.MaNV = rd.GetInt32(6);
                hdDTO.LoaiKH = rd.GetBoolean(7);

                lst.Add(hdDTO);
            }
            conn.Close();

            return lst;
        }
        public List<DTO.HoaDonDTO> LayHoaDonNVTheoNgay(DateTime batdau, DateTime ketthuc)
        {

            List<DTO.HoaDonDTO> lst = new List<HoaDonDTO>();

            OleDbConnection conn = DataProvider.ConnectDB();
            conn.Open();
            string sql = "select hd.MaHD,hd.NgayHD,hd.SoHD,hd.MaKH,hd.TenKH,hd.ThanhTien,hd.MaNV,hd.LoaiKH, (select nv.HoTen from NhanVien nv where nv.MaNV=hd.MaNV) as TenNV  from HoaDon as hd where hd.NgayHD between " + "#" + batdau.ToShortDateString() + "#" + " and " + "#" + ketthuc.ToShortDateString() + "# and hd.LoaiKH=true";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                DTO.HoaDonDTO hdDTO = new HoaDonDTO();
                hdDTO.MaHD = rd.GetInt32(0);
                hdDTO.NgayHD = rd.GetDateTime(1);
                hdDTO.SoHD = rd.GetString(2);
                hdDTO.MaKH = rd.GetInt32(3);
                hdDTO.TenKH = rd.GetString(4);
                hdDTO.ThanhTien = rd.GetDouble(5);
                hdDTO.MaNV = rd.GetInt32(6);
                hdDTO.LoaiKH = rd.GetBoolean(7);
                hdDTO.TenNV = rd.GetString(8);
                lst.Add(hdDTO);
            }
            conn.Close();

            return lst;
        }
        public List<DTO.HoaDonDTO> LayHoaDonKLTheoNgay(DateTime batdau, DateTime ketthuc)
        {

            List<DTO.HoaDonDTO> lst = new List<HoaDonDTO>();

            OleDbConnection conn = DataProvider.ConnectDB();
            conn.Open();
            string sql = "select hd.MaHD,hd.NgayHD,hd.SoHD,hd.MaKH,hd.TenKH,hd.ThanhTien,hd.MaNV,hd.LoaiKH, (select nv.HoTen from NhanVien nv where nv.MaNV=hd.MaNV) as TenNV  from HoaDon as hd where hd.NgayHD between " + "#" + batdau.ToShortDateString() + "#" + " and " + "#" + ketthuc.ToShortDateString() + "# and hd.LoaiKH=false";
         
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                DTO.HoaDonDTO hdDTO = new HoaDonDTO();
                hdDTO.MaHD = rd.GetInt32(0);
                hdDTO.NgayHD = rd.GetDateTime(1);
                hdDTO.SoHD = rd.GetString(2);
                hdDTO.MaKH = rd.GetInt32(3);
                hdDTO.TenKH = rd.GetString(4);
                hdDTO.ThanhTien = rd.GetDouble(5);
                hdDTO.MaNV = rd.GetInt32(6);
                hdDTO.LoaiKH = rd.GetBoolean(7);
                hdDTO.TenNV = rd.GetString(8);
                lst.Add(hdDTO);
            }
            conn.Close();

            return lst;
        }
        //
        //Insert HoaDon
        //
        public int ThemHoaDon()
        {
            string sql = "";
            int kq;
            try
            {
                sql="Insert into HoaDon(NgayHD) values('" + DateTime.Now.ToShortDateString() + "')";
                OleDbConnection conn = DataProvider.ConnectDB();
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;

                kq = cmd.ExecuteNonQuery();
            }
            catch
            {
                throw new Exception(sql);
            }
            return kq;
        }
        public int LayMaHoaDonMoi()
        {
            HoaDonDTO hd = new HoaDonDTO();
            string sql = "";
            int kq;
         //   try
         //   {
                 sql = "select MAX(MaHD) from HoaDon ";
                OleDbConnection conn = DataProvider.ConnectDB();
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                return (int)cmd.ExecuteScalar();
                
            //}
            //catch
            //{
            //    throw new Exception(sql);
            //}
      

        }
    }
}
